local tonumber,tostring,assert,select=
	  tonumber,tostring,assert,select
local os_time=
	  os.time
local string_match,string_format=
	  string.match,string.format
require'cosmo'
require'zdc.deflate'
module(..., package.seeall)
local dbcfun={
	id='id',
	pid='pid',
	ik='ik',
	ts='ts',
	AND=function(...)
		local cnt=select('#',...)
		local s='(' .. select(1,...)
		for i=2,cnt do
			s=s .. ' and ' .. select(i,...)
		end
		s=s..')'
		return s
	end,
	OR=function(...)
		local cnt=select('#',...)
		local s='(' .. select(1,...)
		for i=2,cnt do
			s=s .. ' or ' .. select(i,...)
		end
		s=s..')'
		return s
	end,
	GT=function(k,a)
		return k .. '>'..a
	end,
	LT=function(k,a)
		return k .. '<'..a
	end,
	GE=function(k,a)
		return k .. '>='..a
	end,
	LE=function(k,a)
		return k .. '<='..a
	end,
	EQ=function(k,a)
		return k .. '=='..a
	end,
}
local _wheremeta= {__index=dbcfun}

function _getdbwherefun(where,whereenv)
	if not where or #where==0 then return end
	local f=assert(loadstring('return '..where))
	setfenv(f,whereenv)
	return f()
end


local dbcsub = {}
local dbcsub_mt = {__metatable = {}, __index = dbcsub}

function MakeInhMT(ch)
	ch=ch or {}
	for _i,_j in pairs(dbcsub) do
		ch[_i]=_j
	end
	return ch
end

function new(db,schemas,tablenode)
	local base=db
	local subs=base._sub or {}
	if subs[tablenode] then return subs[tablenode] end
	local obj={schemas=schemas,tablenode=tablenode,dbc=base}
	setmetatable(obj, dbcsub_mt)
	obj._whereenv=setmetatable({_esc=function(v) return base:prepare_val(v) end},_wheremeta)
	obj:init()
	subs[tablenode]=obj
	base._sub=subs
	return obj
end

function dbcsub:close()
	local base=self.dbc
	base._sub[self.tablenode]=nil
	if next(base._sub) then return end
	base.con:close()
end

local zdc_deflate_node=zdc.deflate.deflate_node
function dbcsub:deflate_node(a,b,c)
	return zdc_deflate_node(a,b,c)
end

function dbcsub:init()
	self:_init_query(self.schemas,self.tablenode)
end

function dbcsub:_init_query(schemas,tablenode)
	local dbc=self.dbc
	local obj=self
	obj.tables = {}
	local con=dbc.con

	-- Create the two data tables, if they don't already exist
	local tbl = tablenode
	obj.tables.node=tbl
	local cosmoarg={tablename=obj.tables.node}
	if type(schemas.node)=='table' then
		for i,v in ipairs(schemas.node) do
			local cmd=dbc:prepare(cosmo.fill(v,cosmoarg))
			assert(con:execute(cmd))
		end
	else
		local cmd=dbc:prepare(cosmo.fill(schemas.node,cosmoarg))
		assert(con:execute(cmd))
	end
	if schemas.label then
		obj.tables.label=obj.tables.node..'_label'
		if type(schemas.label)=='table' then
			for i,v in ipairs(schemas.label) do
				local cmd=dbc:prepare(cosmo.fill(v,cosmoarg))
				assert(con:execute(cmd))
			end
		else
			local cmd=dbc:prepare(cosmo.fill(schemas.label,cosmoarg))
			assert(con:execute(cmd))
		end
	end
	

	-- Pre-build our queries
	obj.queries = {
		GET_LABEL_ = string.format("SELECT id,idx,sum,timestamp,usr from %s WHERE id = %%s", obj.tables.label),
		SET_LABEL = string.format("REPLACE INTO %s (id,idx,sum,timestamp,usr) VALUES (%%s, %%s, %%s, %%s, %%s);", obj.tables.label),
			
		GET_NODE_ = string_format("SELECT id,data,ik,ts from %s WHERE id = %%s;", obj.tables.node),
		GET_NODES = string_format("SELECT id,data,ik,ts FROM %s;", obj.tables.node),
		GET_IDS = string_format("SELECT id FROM %s;", obj.tables.node),
		GET_CNT = string_format("SELECT count(*) FROM %s;", obj.tables.node),
		
		GET_CHILDNODES=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%s;", obj.tables.node),
		GET_CHILDNODES_w_s_l=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s and %%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDNODES_w_l=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s and %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDNODES_w_s=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s and %%s ORDER BY %%s;", obj.tables.node),
		GET_CHILDNODES_w=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s and %%s;", obj.tables.node),
		GET_CHILDNODES_s_l=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDNODES_s=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s ORDER BY %%s;", obj.tables.node),
		GET_CHILDNODES_l=string_format("SELECT id,data,ik,ts from %s WHERE pid = %%%%s %s;", obj.tables.node,schemas.sqlex_limit),


		GET_PREFIXNODES=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%s;", obj.tables.node),
		GET_PREFIXNODES_w_s_l=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s and %%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXNODES_w_l=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s and %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXNODES_w_s=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s and %%s ORDER BY %%s;", obj.tables.node),
		GET_PREFIXNODES_w=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s and %%s;", obj.tables.node),
		GET_PREFIXNODES_s_l=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXNODES_s=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s ORDER BY %%s;", obj.tables.node),
		GET_PREFIXNODES_l=string_format("SELECT id,data,ik,ts from %s WHERE id LIKE %%%%s %s;", obj.tables.node,schemas.sqlex_limit),

		GET_CHILDIDS=string_format("SELECT id from %s WHERE pid = %%s;", obj.tables.node),
		GET_CHILDIDS_w_s_l=string_format("SELECT id from %s WHERE pid = %%%%s and %%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDIDS_w_l=string_format("SELECT id from %s WHERE pid = %%%%s and %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDIDS_w_s=string_format("SELECT id from %s WHERE pid = %%%%s and %%s ORDER BY %%s;", obj.tables.node),
		GET_CHILDIDS_w=string_format("SELECT id from %s WHERE pid = %%%%s and %%s;", obj.tables.node),
		GET_CHILDIDS_s_l=string_format("SELECT id from %s WHERE pid = %%%%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDIDS_s=string_format("SELECT id from %s WHERE pid = %%%%s ORDER BY %%s;", obj.tables.node),
		GET_CHILDIDS_l=string_format("SELECT id from %s WHERE pid = %%%%s %s;", obj.tables.node,schemas.sqlex_limit),


		GET_PREFIXIDS=string_format("SELECT id from %s WHERE id LIKE %%s;", obj.tables.node),
		GET_PREFIXIDS_w_s_l=string_format("SELECT id from %s WHERE id LIKE %%%%s and %%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXIDS_w_l=string_format("SELECT id from %s WHERE id LIKE %%%%s and %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXIDS_w_s=string_format("SELECT id from %s WHERE id LIKE %%%%s and %%s ORDER BY %%s;", obj.tables.node),
		GET_PREFIXIDS_w=string_format("SELECT id from %s WHERE id LIKE %%%%s and %%s;", obj.tables.node),
		GET_PREFIXIDS_s_l=string_format("SELECT id from %s WHERE id LIKE %%%%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXIDS_s=string_format("SELECT id from %s WHERE id LIKE %%%%s ORDER BY %%s;", obj.tables.node),
		GET_PREFIXIDS_l=string_format("SELECT id from %s WHERE id LIKE %%%%s %s;", obj.tables.node,schemas.sqlex_limit),

		GET_CHILDCNT=string_format("SELECT count(*) from %s WHERE pid = %%s;", obj.tables.node),
		GET_CHILDCNT_w_s_l=string_format("SELECT count(*) from %s WHERE pid = %%%%s and %%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDCNT_w_l=string_format("SELECT count(*) from %s WHERE pid = %%%%s and %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDCNT_w_s=string_format("SELECT count(*) from %s WHERE pid = %%%%s and %%s ORDER BY %%s;", obj.tables.node),
		GET_CHILDCNT_w=string_format("SELECT count(*) from %s WHERE pid = %%%%s and %%s;", obj.tables.node),
		GET_CHILDCNT_s_l=string_format("SELECT count(*) from %s WHERE pid = %%%%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_CHILDCNT_s=string_format("SELECT count(*) from %s WHERE pid = %%%%s ORDER BY %%s;", obj.tables.node),
		GET_CHILDCNT_l=string_format("SELECT count(*) from %s WHERE pid = %%%%s %s;", obj.tables.node,schemas.sqlex_limit),


		GET_PREFIXCNT=string_format("SELECT count(*) from %s WHERE id LIKE %%s;", obj.tables.node),
		GET_PREFIXCNT_w_s_l=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s and %%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXCNT_w_l=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s and %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXCNT_w_s=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s and %%s ORDER BY %%s;", obj.tables.node),
		GET_PREFIXCNT_w=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s and %%s;", obj.tables.node),
		GET_PREFIXCNT_s_l=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s ORDER BY %%s %s;", obj.tables.node,schemas.sqlex_limit),
		GET_PREFIXCNT_s=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s ORDER BY %%s;", obj.tables.node),
		GET_PREFIXCNT_l=string_format("SELECT count(*) from %s WHERE id LIKE %%%%s %s;", obj.tables.node,schemas.sqlex_limit),


		NODE_EXISTS = string_format("SELECT DISTINCT id FROM %s WHERE id = %%s;", obj.tables.node),
		INSERT_NODE = string_format("REPLACE INTO %s (id,pid,ik,ts,data) VALUES (%%s,%%s,%%s,%%s,%%s);", obj.tables.node),
		REMOVE_NODE = string_format("DELETE from %s WHERE id = %%s", obj.tables.node),
		REMOVE_SUBNODE = string_format("DELETE FROM %s WHERE id LIKE %%s;", obj.tables.node),
	}
	return
end

function dbcsub:transdo(fn)
	assert(fn)
	return self.dbc:transdo(fn)
end

function dbcsub:node_exists(id)
	assert(id)
	local dbc=self.dbc

	local cmd = dbc:prepare(self.queries.NODE_EXISTS, id)
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "*a")
	cur:close()

	return row ~= nil
end

function dbcsub:get_node(id)
	assert(id)
	local dbc=self.dbc

	-- Get the most recent version of the node
	local cmd = dbc:prepare(self.queries.GET_NODE_, id)

	-- Run the query to get the node
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")
	cur:close()
	if not row then
		return nil
	end
	assert(row.data)
	row.ik=tonumber(row.ik)
	row.ts=tonumber(row.ts)
	return row
end

function dbcsub:save_node(id, data, ik, ts)
	assert (id)
	assert(data)
	local dbc=self.dbc
	
	local parent,rest=string_match(id, "^(.+)/(.-)$")
	parent=parent or ''
	ik=ik or 0
	-- generate and save the new index
	ts = ts or os_time() -- default to current time

	-- Store the new node in the 'nodes' table
	local cmd = dbc:prepare(self.queries.INSERT_NODE, id, parent, ik, ts, data)
	local cur,err = assert(dbc:execute(cmd))
end

local function __get_cmd_wsl(self,ty,where,sort,limit)
	local cmd
	if where and #where==0 then where=nil end
	if sort and #sort==0 then sort=nil end
	if type(limit)=='string' and #limit==0 then limit=nil end
	if where then
		if sort then
			if limit then
				cmd=string_format(self.queries[ty..'_w_s_l'],_getdbwherefun(where,self._whereenv),string.gsub(sort,'%-',' desc'),limit)
			else
				cmd=string_format(self.queries[ty..'_w_s'],_getdbwherefun(where,self._whereenv),string.gsub(sort,'%-',' desc'))
			end
		else
			if limit then
				cmd=string_format(self.queries[ty..'_w_l'],_getdbwherefun(where,self._whereenv),limit)
			else
				cmd=string_format(self.queries[ty..'_w'],_getdbwherefun(where,self._whereenv))
			end
		end
	else
		if sort then
			if limit then
				cmd=string_format(self.queries[ty..'_s_l'],string.gsub(sort,'%-',' desc'),limit)
			else
				cmd=string_format(self.queries[ty..'_s'],string.gsub(sort,'%-',' desc'))
			end
		else
			if limit then
				cmd=string_format(self.queries[ty..'_l'],limit)
			else
				return nil
			end
		end
	end
	return cmd
end

function dbcsub:get_child_nodes(parentid)
	local dbc=self.dbc
	
	local nodes = {}
	local cmd = dbc:prepare(self.queries.GET_CHILDNODES,parentid)

	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "a")

	while row do
		row.ik=tonumber(row.ik)
		row.ts=tonumber(row.ts)
		nodes[row.id] = row
		row = cur:fetch({}, "a")
	end

	cur:close()
	return nodes
end
local function autolstgen(a)
	local lst={}
	for k,v in pairs(a) do lst[#lst+1]=k end
	return a,lst
end

function dbcsub:get_child_nodes_ex(parentid,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_CHILDNODES',where,sort,limit)
	if not cmd then return autolstgen(self:get_child_nodes(parentid)) end
	local dbc=self.dbc
	local nodes,lst = {},{}
	cmd=dbc:prepare(cmd,parentid)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "a")

	while row do
		row.ik=tonumber(row.ik)
		row.ts=tonumber(row.ts)
		nodes[row.id] = row
		lst[#lst+1]=row.id
		row = cur_fetch(cur,{}, "a")
	end

	cur:close()
	return nodes,lst
end

function dbcsub:get_nodes_by_prefix(prefix)
	local dbc=self.dbc
	local nodes = {}
	local cmd
	if prefix then
		prefix = prefix .. "%"
		cmd = dbc:prepare(self.queries.GET_PREFIXNODES, prefix)
	else
		cmd = dbc:prepare(self.queries.GET_NODES)
	end

	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "a")

	while row do
		row.ik=tonumber(row.ik)
		row.ts=tonumber(row.ts)
		nodes[row.id] = row
		row = cur_fetch(cur,{}, "a")
	end

	cur:close()
	return nodes
end

function dbcsub:get_nodes_by_prefix_ex(prefix,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_PREFIXNODES',where,sort,limit)
	if not cmd then return autolstgen(self:get_nodes_by_prefix(prefix)) end

	local dbc=self.dbc
	local nodes,lst = {},{}
	prefix = (prefix or '') .. "%"
	cmd=dbc:prepare(cmd,prefix)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "a")

	while row do
		row.ik=tonumber(row.ik)
		row.ts=tonumber(row.ts)
		nodes[row.id] = row
		lst[#lst+1]=row.id
		row = cur_fetch(cur,{}, "a")
	end

	cur:close()
	return nodes,lst
end

function dbcsub:get_ids_by_prefix(prefix)
	local dbc=self.dbc
	local nodes = {}
	local cmd
	if prefix then
		prefix = prefix .. "%"
		cmd = dbc:prepare(self.queries.GET_PREFIXIDS, prefix)
	else
		cmd = dbc:prepare(self.queries.GET_IDS)
	end

	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	while row do
		table.insert(nodes,row[1])
		row = cur_fetch(cur,row, "n")
	end
	
	cur:close()
	return nodes
end

function dbcsub:get_ids_by_prefix_ex(prefix,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_PREFIXIDS',where,sort,limit)
	if not cmd then return self:get_ids_by_prefix(prefix) end

	local dbc=self.dbc
	local nodes = {}
	prefix = (prefix or '') .. "%"
	cmd=dbc:prepare(cmd,prefix)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	while row do
		table.insert(nodes,row[1])
		row = cur_fetch(cur,row, "n")
	end

	cur:close()
	return nodes
end

function dbcsub:get_child_ids(parentid)
	local dbc=self.dbc
	local cmd = dbc:prepare(self.queries.GET_CHILDIDS, parentid)
	local nodes = {}
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	while row do
		table.insert(nodes,row[1])
		row = cur_fetch(cur,row, "n")
	end
	
	cur:close()
	return nodes
end

function dbcsub:get_child_ids_ex(parentid,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_CHILDIDS',where,sort,limit)
	if not cmd then return self:get_child_ids(parentid) end

	local dbc=self.dbc
	local nodes = {}
	cmd=dbc:prepare(cmd,parentid)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	while row do
		table.insert(nodes,row[1])
		row = cur_fetch(cur,row, "n")
	end

	cur:close()
	return nodes
end

function dbcsub:get_cnt_by_prefix(prefix)
	local dbc=self.dbc
	local cnt=0
	local cmd
	if prefix then
		prefix = prefix .. "%"
		cmd = dbc:prepare(self.queries.GET_PREFIXCNT, prefix)
	else
		cmd = dbc:prepare(self.queries.GET_CNT)
	end

	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	cnt=row[1]
	cur:close()
	return tonumber(cnt)
end

function dbcsub:get_cnt_by_prefix_ex(prefix,where)
	local cmd=where and __get_cmd_wsl(self,'GET_PREFIXCNT',where)
	if not cmd then return self:get_cnt_by_prefix(prefix) end

	local dbc=self.dbc
	local cnt=0
	prefix = (prefix or '') .. "%"
	cmd=dbc:prepare(cmd,prefix)
	print(cmd)
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	cnt=row[1]

	cur:close()
	return tonumber(cnt)
end


function dbcsub:get_child_cnt(parentid)
	local dbc=self.dbc
	local cnt=0
	local cmd = dbc:prepare(self.queries.GET_CHILDCNT, parentid)

	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	cnt=row[1]
	cur:close()
	return tonumber(cnt)
end

function dbcsub:get_child_cnt_ex(parentid,where)
	local cmd=where and __get_cmd_wsl(self,'GET_CHILDCNT',where)
	if not cmd then return self:get_child_cnt(parentid) end

	local dbc=self.dbc
	local cnt=0
	cmd=dbc:prepare(cmd,parentid)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local cur_fetch=cur.fetch
	local row = cur_fetch(cur,{}, "n")
	cnt=row[1]

	cur:close()
	return tonumber(cnt)
end

function dbcsub:del_node(id,deep)
	local dbc=self.dbc
	local cmd = dbc:prepare(self.queries.REMOVE_NODE, id)
	local cur = assert(dbc:execute(cmd))
	if deep then
		cmd = dbc:prepare(self.queries.REMOVE_SUBNODE, id..'/%')
		cur = assert(dbc:execute(cmd))
	end
end

function dbcsub:del_sub_nodes(parentid)
	local dbc=self.dbc
	cmd = dbc:prepare(self.queries.REMOVE_SUBNODE, parentid..'/%')
	cur = assert(dbc:execute(cmd))
end


function dbcsub:get_label(id)
	assert(id)
	local dbc=self.dbc
	-- Get the most recent version of the node
	local cmd = dbc:prepare(self.queries.GET_LABEL_, id)

	-- Run the query to get the node
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")
	cur:close()
	if not row then
		return 0,'',''
	end
	return row.idx,row.sum,row.timestamp,row.usr
end

function dbcsub:set_label(id, idx,sum,timestamp,usr)
	assert (id)
	assert(idx)
	assert(sum)
	--assert(timestamp)
	--assert(usr)
	-- Store the new node in the 'nodes' table
	local dbc=self.dbc
	local cmd = dbc:prepare(self.queries.SET_LABEL, id, idx, sum, timestamp,usr)
	local cur,err = assert(dbc:execute(cmd), out)
end
function dbcsub:get_nodes_sum(rootid, sumfun)
	return sumfun(nil,rootid,'')
end